"""Quick database inspection script.

Purpose: Directly read the current SQLite (or configured) database and print
all table names and their rows. Intended for development diagnostics, not for
production.

Run:
    python backend/tests/dump_db.py

Optionally set DATABASE_URL / .env before running (it uses app.config.settings).
"""

from __future__ import annotations

from sqlalchemy import inspect, text
from sqlalchemy.engine import Engine

# --- Ensure backend root (one level up) is on sys.path so `import app` works
import sys, os
from pathlib import Path

CURRENT_DIR = Path(__file__).resolve().parent
BACKEND_ROOT = CURRENT_DIR.parent  # backend/
if str(BACKEND_ROOT) not in sys.path:
    sys.path.insert(0, str(BACKEND_ROOT))

from app.config import settings  # noqa: E402
from app.database import engine, Base  # noqa: E402
from app import models  # noqa: E402,F401  (register models)


def _db_file_from_url(url: str) -> str | None:
    if url.startswith("sqlite:///"):
        return url.replace("sqlite:///", "")
    return None


def _candidate_paths(db_file: str) -> list[Path]:
    """Return possible filesystem locations for a relative sqlite path.

    If you run the server from backend/ the relative file is backend/data/...;
    if you run this script from backend/tests/ the relative file becomes
    backend/tests/data/.... This caused user confusion when tables appear empty.
    We list both so it's obvious which file actually has rows.
    """
    p = Path(db_file)
    if p.is_absolute():
        return [p]
    # path relative to current working dir (where script was executed)
    cwd = Path.cwd() / p
    # path relative to backend root
    backend_rel = BACKEND_ROOT / p
    candidates = []
    seen = set()
    for c in [cwd, backend_rel]:
        if c not in seen:
            candidates.append(c)
            seen.add(c)
    return candidates


def dump_all(engine: Engine):
    insp = inspect(engine)
    tables = insp.get_table_names()
    db_file = _db_file_from_url(settings.DATABASE_URL)
    if db_file:
        candidates = _candidate_paths(db_file)
        print("Database file candidates (relative path ambiguity diagnostic):")
        for c in candidates:
            print(
                f" - {c.resolve()} (exists={c.exists()} size={c.stat().st_size if c.exists() else 'n/a'})"
            )
        if len(candidates) > 1:
            print(
                "NOTE: If your server was started from 'backend/' but you run this script from 'backend/tests', you are looking at a DIFFERENT sqlite file. Use an absolute DATABASE_URL to avoid this (see suggestion below)."
            )

    if not tables:
        print("No tables reported in metadata inspector; attempting create_all() ...")
        Base.metadata.create_all(bind=engine)
        insp = inspect(engine)
        tables = insp.get_table_names()
        if not tables:
            print(
                "Still no tables. Possible causes: 1) models not imported 2) using different working directory for server so another DB was created 3) wrong DATABASE_URL in .env."
            )
            print(f"DATABASE_URL currently: {settings.DATABASE_URL}")
            return

    print(f"DATABASE_URL: {settings.DATABASE_URL}")
    print(f"Found tables: {tables}\n")
    if db_file:
        print(
            "Suggestion: set DATABASE_URL to an absolute path to avoid multiple copies, e.g.\n"
            "  DATABASE_URL=sqlite:///"
            + str((BACKEND_ROOT / "data" / "database.db").resolve())
        )

    with engine.connect() as conn:
        for table in tables:
            print(f"=== TABLE: {table} ===")
            try:
                result = conn.execute(text(f"SELECT * FROM {table}"))
                rows = result.fetchall()
            except Exception as e:  # pragma: no cover - defensive
                print(f"Error reading table {table}: {e}")
                continue
            if not rows:
                print("(empty)")
            else:
                for row in rows:
                    # SQLAlchemy 2.0 RowMapping access
                    mapping = dict(row._mapping)
                    print(mapping)
            print()


if __name__ == "__main__":
    dump_all(engine)
